package com.distributor;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;



public class DataHelper 
{
	 private static final String DATABASE_NAME = "csr.db";
	   private static final int DATABASE_VERSION = 3;
	   private static final String TABLE_RESELLER_FAVOURITE="reseller_fav"; 
	   private Context context;
	   private SQLiteDatabase db; 
	   private SQLiteStatement insertStmtFav;
	   
	   private static final String INSERT_RESELLER_FAVOURITE = "insert into "
		  + TABLE_RESELLER_FAVOURITE + "(AccountName,Balance,ResellerLevel,IDProofType,IDNumber," +
		  								"ResellerCategory,BillingTemplate,SaleExecutive,DiscountMatrix,Name," +
		  								"Phone1,Phone2,IMEI,AltPhone,Email,Address1,Address2,AreaName,AreaZipCode,State,StateName,MaxLevel) " +
		  								"values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	   OpenHelper openHelper;
	   
	   public DataHelper(Context context) 
	   {	 
	      this.context = context;
	      openHelper = new OpenHelper(this.context);
	      this.db = openHelper.getWritableDatabase(); 
	      this.insertStmtFav=this.db.compileStatement(INSERT_RESELLER_FAVOURITE);
	   }
	   public long insertToFav(ResellerInfo resellerInfo) 
	   { 
		   
		  this.insertStmtFav.bindString(1,resellerInfo.getAccountName());
		  this.insertStmtFav.bindString(2, resellerInfo.getBalance());
		  this.insertStmtFav.bindString(3, resellerInfo.getResellerLevel());
		  this.insertStmtFav.bindString(4,resellerInfo.getIDProofType()); 
		  this.insertStmtFav.bindString(5,resellerInfo.getIDNumber());
		  this.insertStmtFav.bindString(6,resellerInfo.getResellerCategory());
		  this.insertStmtFav.bindString(7,resellerInfo.getBillingTemplate());
		  this.insertStmtFav.bindString(8,resellerInfo.getSaleExecutive());
		  this.insertStmtFav.bindString(9,resellerInfo.getDiscountMatrix()); 
		  this.insertStmtFav.bindString(10,resellerInfo.getName());
		  this.insertStmtFav.bindString(11, resellerInfo.getPhone1());
		  this.insertStmtFav.bindString(12, resellerInfo.getPhone2());
		  this.insertStmtFav.bindString(13,resellerInfo.getIMEI());
		  this.insertStmtFav.bindString(14, resellerInfo.getAltPhone());
		  this.insertStmtFav.bindString(15,resellerInfo.getEmail());
		  this.insertStmtFav.bindString(16,resellerInfo.getAddress1());
		  this.insertStmtFav.bindString(17,resellerInfo.getAddress2());
		  this.insertStmtFav.bindString(18,resellerInfo.getAreaName());
		  this.insertStmtFav.bindString(19, resellerInfo.getAreaZipCode());
		  this.insertStmtFav.bindString(20,resellerInfo.getState());
		  this.insertStmtFav.bindString(21, resellerInfo.getStateName());
		  this.insertStmtFav.bindString(22,resellerInfo.getMaxlevel());

		  return this.insertStmtFav.executeInsert();
	      
	   }
	   

	   public boolean checkInFav(ResellerInfo resellerInfo)
	   {
		   
		   Cursor cursor= db.query(TABLE_RESELLER_FAVOURITE,  new String[] {"AccountName"}, "IMEI = ?",new String[]{resellerInfo.getIMEI()},  null, null, null);
		 
		   if(cursor.getCount()>0)
		      {
			      if (cursor.moveToFirst())
			           	System.out.println(cursor.getString(0) +" is in fav");
				  
			      if (cursor != null && !cursor.isClosed())
			      {
			         cursor.close();
			      }
			      System.out.println("Returning True..");
			      return true;
		      }
		      else
		      {
		    	  System.out.println(resellerInfo.getAccountName()+ " not avilable in fav");
		    	  return false;
		      }
	   }
	   private static class OpenHelper extends SQLiteOpenHelper 
	   { 
		  OpenHelper(Context context) 
	      {
	         super(context, DATABASE_NAME, null, DATABASE_VERSION);
	      }
	 
	      @Override
	      public void onCreate(SQLiteDatabase db) 
	      { 
	    	  db.execSQL("CREATE TABLE " + TABLE_RESELLER_FAVOURITE + "(id INTEGER PRIMARY KEY, AccountName TEXT, Balance TEXT, ResellerLevel TEXT, IDProofType TEXT, IDNumber TEXT, " +
	    	  														  "	ResellerCategory TEXT, BillingTemplate TEXT, SaleExecutive TEXT, DiscountMatrix TEXT,Name TEXT, Phone1 TEXT, Phone2 TEXT, " +
	    	  														  " IMEI TEXT,AltPhone TEXT, Email TEXT, Address1 TEXT, Address2 TEXT,AreaName TEXT,AreaZipCode TEXT, State TEXT, StateName TEXT, MaxLevel TEXT)");
	      }
	      
	      @Override
	      public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
	      {
	         Log.w("Example", "Upgrading database, this will drop tables and recreate.");
	         db.execSQL("DROP TABLE IF EXISTS " + TABLE_RESELLER_FAVOURITE);
	         onCreate(db);
	      }
	      
	   }

	   public void close() 
	   {
	       if (openHelper != null) 
	       {
	    	   	openHelper.close();
	       }
	   }
	public void deleteProdctFromFav(ResellerInfo agentInfo) 
	{
		int i=this.db.delete(TABLE_RESELLER_FAVOURITE, "IMEI"+" =?", new String[] {agentInfo.getIMEI()});
		System.out.println(i+ "Row is deleted");
	}
	public ArrayList<ResellerInfo> getFavTopUpProducts(int level) 
	{
		    ArrayList<ResellerInfo> list = new ArrayList<ResellerInfo>();
//			   Cursor cursor= db.query(TABLE_RESELLER_FAVOURITE,  new String[] { "systemSerId","systemSerName","productID","productName","productDesc","productType","batchID","minVal","maxVal","ImageURL" }
//			   ,"systemSerId" +" = ?", new String[]{""+level},  null, null, null);
		    Cursor cursor=db.rawQuery("select * from "+TABLE_RESELLER_FAVOURITE+ " where ResellerLevel = ?", new String[] {""+level});
			   	  
		    System.out.println("Cursor has:"+cursor.getCount() +"Records.");
		    if(cursor.getCount()>0)
			      { 
			   		  
				      if (cursor.moveToFirst())
				      {
				         do 
				         {
				        	 
				        	ResellerInfo p=new ResellerInfo();
				            p.setAccountName (cursor.getString(1));
					        p.setBalance (cursor.getString(2));
					        p.setResellerLevel (cursor.getString(3));
					        p.setIDProofType (cursor.getString(4));
					        p.setIDNumber (cursor.getString(5));
					        p.setResellerCategory (cursor.getString(6));
					        p.setBillingTemplate (cursor.getString(7));
					        p.setSaleExecutive (cursor.getString(8));
					        p.setDiscountMatrix (cursor.getString(9));
					        p.setName(cursor.getString(10));
					        p.setPhone1(cursor.getString(11));
					        p.setPhone2 (cursor.getString(12));
					        p.setIMEI (cursor.getString(13));
					        p.setAltPhone(cursor.getString(14));
					        p.setEmail (cursor.getString(15));
					        p.setAddress1 (cursor.getString(16));
					        p.setAddress2 (cursor.getString(17));
					        p.setAreaName(cursor.getString(18));
					        p.setAreaZipCode(cursor.getString(19));
					        p.setState (cursor.getString(20));
					        p.setStateName  (cursor.getString(21));
					        p.setMaxlevel(cursor.getString(22));
					        list.add(p);
				        	System.out.println(p.getAccountName()+" retrived from database.");
				         }
				         while (cursor.moveToNext());
				      }
				      if(cursor != null && !cursor.isClosed())
				      {
				         cursor.close();
				      }
				      return list;
			      }
			      else
			      {
			    	  System.out.println("No Favourites Avilable.");
			    	  return list;
			      }
		   
	}
}
